package com.food.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.RecipeDAO;
import com.food.model.Product;
import com.food.model.Recipe;

public class RecipeDAOJdbc implements RecipeDAO {
//	private static final String URL = "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
//	private static final String USER = "sa";
//	private static final String PASSWORD = "sa123456";
	
	private DataSource ds = null;
	public RecipeDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	private static final String INSERT ="insert into recipe (mbid, rname, countryid, rintroduce, rtrick, rimg, rdate) values(?,?,?,?,?,?,?)";
	@Override
	public int insertRecipe(Recipe bean,InputStream is, int size) {
		int key =-1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(INSERT,Statement.RETURN_GENERATED_KEYS);
			pstmt.setInt(1, bean.getMbid());
			pstmt.setString(2, bean.getRname());
			pstmt.setInt(3, bean.getCountryid());
			pstmt.setString(4,bean.getRintroduce());
			pstmt.setString(5,bean.getRtrick());
			pstmt.setBinaryStream(6, is, size);
			pstmt.setDate(7, bean.getRdate());
			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();
			if(rs.next()){
				key = rs.getInt(1);
			}
			return key;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return key;
	}
	private static final String UPDATE ="update recipe set mbid =?, rname =?, countryid =?, rintroduce =?, rtrick =?,rimg =?, rdate =? where rid=?";
	@Override
	public void updateRecipe(int mbid, String rname, int countryid,
			String rintroduce, String rtrick, byte[] rimg, Date rdate, int rid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(UPDATE);
			pstmt.setInt(1, rid);
			pstmt.setString(2, rname);
			pstmt.setInt(3, countryid);
			pstmt.setString(4, rintroduce);
			pstmt.setString(5, rtrick);
			pstmt.setBytes(6, rimg);
			pstmt.setDate(7, rdate);
			pstmt.setInt(8, rid);
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}
	private static final String DELETE ="delete from recipe where rid=?";
	@Override
	public void deleteRecipe(int rid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(DELETE);
			pstmt.setInt(1, rid);
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	private static final String SELECTAllRecipe = "select rid,mbid, rname, countryid, rintroduce, rtrick, rimg, rdate,rscore from recipe";
	@Override
	public List<Recipe> findAllRecipe() {
		List<Recipe> result = null;
		Recipe bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTAllRecipe);
			rs = pstmt.executeQuery();
			result = new ArrayList<Recipe>();
			while(rs.next()){
				bean = new Recipe();
				bean.setRid(rs.getInt("rid"));
				bean.setMbid(rs.getInt("mbid"));
				bean.setRname(rs.getString("rname"));
				bean.setCountryid(rs.getInt("countryid"));
				bean.setRintroduce(rs.getString("rintroduce"));
				bean.setRtrick(rs.getString("rtrick"));
				bean.setRimg(rs.getBytes("rimg"));
				bean.setRdate(rs.getDate("rdate"));
				bean.setRscore(rs.getDouble("rscore"));
				result.add(bean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	
	//page
	@Override
	public List<Recipe> findAllRecipePages(int pagesize, int pageindex) {
		List<Recipe> result = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		String sql1 = "select top " + pagesize
				+ " rid, mbid, rname, countryid, rintroduce, rtrick, rimg, rdate,rscore ";
		String sql2 = "from recipe where rid not in ( select top (" + pagesize
				+ "*" + pageindex + ") rid from recipe order by rid desc ) ";
		String sql3 = "order by rid desc";
		
		
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(sql1 + sql2 + sql3);
			// stmt =
			// conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

			rset = stmt.executeQuery();
			result = new ArrayList<Recipe>();
			while (rset.next()) {
				Recipe temp = new Recipe();
				temp.setRid(rset.getInt("rid"));
				temp.setMbid(rset.getInt("mbid"));
				temp.setRname(rset.getString("rname"));
				temp.setCountryid(rset.getInt("countryid"));
				temp.setRintroduce(rset.getString("rintroduce"));
				temp.setRtrick(rset.getString("rtrick"));
				temp.setRimg(rset.getBytes("rimg"));
				temp.setRdate(rset.getDate("rdate"));
				temp.setRscore(rset.getDouble("rscore"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;

	}
	
	
	
	
	
	
	private static final String SELECTRecipeById = "select rid,mbid, rname, countryid, rintroduce, rtrick, rimg, rdate, rscore from recipe where rid=? order by rid desc";
	@Override
	public Recipe findRecipeById(int rid) {
		
		Recipe bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTRecipeById);
			pstmt.setInt(1,rid);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				bean = new Recipe();
				bean.setRid(rs.getInt("rid"));
				bean.setMbid(rs.getInt("mbid"));
				bean.setRname(rs.getString("rname"));
				bean.setCountryid(rs.getInt("countryid"));
				bean.setRintroduce(rs.getString("rintroduce"));
				bean.setRtrick(rs.getString("rtrick"));
				bean.setRimg(rs.getBytes("rimg"));
				bean.setRdate(rs.getDate("rdate"));
				bean.setRscore(rs.getDouble("rscore"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return bean;
	}
	private static final String SELECTRecipeByName = "select rid,mbid, rname, countryid, rintroduce, rtrick, rimg, rdate, rscore from recipe where rname like ? order by rid desc";
	@Override
	public List<Recipe> findRecipeByName(String rname) {
		List<Recipe> result = null;
		Recipe bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTRecipeByName);
			pstmt.setString(1,"%" + rname + "%");
			rs = pstmt.executeQuery();
			result = new ArrayList<Recipe>();
			while(rs.next()){
				bean = new Recipe();
				bean.setRid(rs.getInt("rid"));
				bean.setMbid(rs.getInt("mbid"));
				bean.setRname(rs.getString("rname"));
				bean.setCountryid(rs.getInt("countryid"));
				bean.setRintroduce(rs.getString("rintroduce"));
				bean.setRtrick(rs.getString("rtrick"));
				bean.setRimg(rs.getBytes("rimg"));
				bean.setRdate(rs.getDate("rdate"));
				bean.setRscore(rs.getDouble("rscore"));
				result.add(bean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	private static final String SELECTRecipeByCountry ="select rid,mbid, rname, countryid, rintroduce, rtrick, rimg, rdate,rscore from recipe where countryid =? order by rid desc";
	@Override
	public List<Recipe> findRecipeByCountry(int countryid) {
		List<Recipe> result = null;
		Recipe bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTRecipeByCountry);
			pstmt.setInt(1,countryid);
			rs = pstmt.executeQuery();
			result = new ArrayList<Recipe>();
			while(rs.next()){
				bean = new Recipe();
				bean.setRid(rs.getInt("rid"));
				bean.setMbid(rs.getInt("mbid"));
				bean.setRname(rs.getString("rname"));
				bean.setCountryid(rs.getInt("countryid"));
				bean.setRintroduce(rs.getString("rintroduce"));
				bean.setRtrick(rs.getString("rtrick"));
				bean.setRimg(rs.getBytes("rimg"));
				bean.setRdate(rs.getDate("rdate"));
				bean.setRscore(rs.getDouble("rscore"));
				result.add(bean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	private static final String SELECTRecipeByMb ="select rid,mbid, rname, countryid, rintroduce, rtrick, rimg, rdate, rscore from recipe where mbid =? order by rid desc";
	public List< Recipe > findRecipeByMb (int mbid){
		List<Recipe> result = null;
		Recipe bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTRecipeByMb);
			pstmt.setInt(1,mbid);
			rs = pstmt.executeQuery();
			result = new ArrayList<Recipe>();
			while(rs.next()){
				bean = new Recipe();
				bean.setRid(rs.getInt("rid"));
				bean.setMbid(rs.getInt("mbid"));
				bean.setRname(rs.getString("rname"));
				bean.setCountryid(rs.getInt("countryid"));
				bean.setRintroduce(rs.getString("rintroduce"));
				bean.setRtrick(rs.getString("rtrick"));
				bean.setRimg(rs.getBytes("rimg"));
				bean.setRdate(rs.getDate("rdate"));
				bean.setRscore(rs.getDouble("rscore"));
				result.add(bean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	private static final String SELECTTenRecipe = "select TOP 10 rid, mbid, rname, countryid, rintroduce, rtrick, rimg, rdate, rscore from recipe order by rid desc";	
	public List<Recipe> findTenNewRecipe(){
		List<Recipe> result = null;
		Recipe bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTTenRecipe);
			rs = pstmt.executeQuery();
			result = new ArrayList<Recipe>();
			while(rs.next()){
				bean = new Recipe();
				bean.setRid(rs.getInt("rid"));
				bean.setMbid(rs.getInt("mbid"));
				bean.setRname(rs.getString("rname"));
				bean.setCountryid(rs.getInt("countryid"));
				bean.setRintroduce(rs.getString("rintroduce"));
				bean.setRtrick(rs.getString("rtrick"));
				bean.setRimg(rs.getBytes("rimg"));
				bean.setRdate(rs.getDate("rdate"));
				bean.setRscore(rs.getDouble("rscore"));
				result.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	private static final String SELECTTopTenRecipe = "select TOP 10 rid, mbid, rname, countryid, rintroduce, rtrick, rimg, rdate, rscore from recipe order by rscore desc";
	public List<Recipe> findTopTenScore(){
		List<Recipe> result = null;
		Recipe bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTTopTenRecipe);
			rs = pstmt.executeQuery();
			result = new ArrayList<Recipe>();
			while(rs.next()){
				bean = new Recipe();
				bean.setRid(rs.getInt("rid"));
				bean.setMbid(rs.getInt("mbid"));
				bean.setRname(rs.getString("rname"));
				bean.setCountryid(rs.getInt("countryid"));
				bean.setRintroduce(rs.getString("rintroduce"));
				bean.setRtrick(rs.getString("rtrick"));
				bean.setRimg(rs.getBytes("rimg"));
				bean.setRdate(rs.getDate("rdate"));
				bean.setRscore(rs.getDouble("rscore"));
				result.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}	
	
	public static void main(String[] args){
//		RecipeDAOJdbc dao = new RecipeDAOJdbc();
//		Recipe bean = new Recipe();
//		
//		File f1 = new File("src/com/emp/model/pee.jpg");
//		FileInputStream fis1;
//		byte[] image1 = null;
//		try {
//		fis1 = new FileInputStream(f1);
//		image1 = new byte[(int) f1.length()];
//		fis1.read(image1);
//		} catch (FileNotFoundException e) {	
//		e.printStackTrace();
//		} catch (IOException e) {
//		e.printStackTrace();
//		}
//		
//		bean.setMbid(3);
//		bean.setRname("�����");
//		bean.setCountryid(1);
//		bean.setRintroduce("�a��");
//		bean.setRtrick("�a��");
//		bean.setRimg(image1);
//		bean.setRdate(new java.sql.Date(100));
//		dao.insertRecipe(bean);
//		dao.updateRecipe(2, "��״�", 1,"�a��", "�a",null, new java.sql.Date(100), 2);
//		dao.deleteRecipe(rid);
		

//		System.out.println(dao.findRecipeByName("���"));
	
	}
}
